<?php

namespace Budget\BudgetBundle\Repository;

use Doctrine\ORM\EntityRepository;
use Budget\BudgetBundle\Entity\Family;
use Budget\BudgetBundle\Entity\FamilyMonth;


class IncomeRepository extends EntityRepository
{
    public function findByIdForFamily(Family $family, $id)
    {
        $qb = $this->createQueryBuilder('i')
                   ->select('i')
                   ->where('i.id = :id')
                   ->andWhere('i.family = :familyId')
                   ->andWhere('i.date_deleted IS NULL')
                   ->setParameter('familyId', $family->getId())
                   ->setParameter('id', $id);

        return $qb->getQuery()->getOneOrNullResult(); 
    }
    
    public function getIncomesForFamilyToJson(Family $family, $page, $limit, $sidx, $sord, $dateTo, $dateFrom)
    {     
        $incomes= $this->get('budget_income')->getIncomesForFamilyToArray($family, $page, $limit, $sidx, $sord, $dateTo, $dateFrom);
        $response  = $this->get('budget_income')->getFormatIncomesToJson($incomes);
        return $response; 
    }
    
    public function getTotalSumIncomesForFamilyQuery(Family $family, $dateTo = null, $dateFrom = null)
    {
        $qb = $this->createQueryBuilder('i')
                ->select('SUM(i.amount) as total')
                ->where('i.family = :family')
                ->andWhere('i.date_deleted IS NULL')
                ->andWhere('i.date_created >= :dateFrom')
                ->andWhere('i.date_created <= :dateTo')
                ->setParameter('family' , $family->getId())
                ->setParameter('dateTo' , $dateTo)
                ->setParameter('dateFrom' , $dateFrom);
        return $qb->getQuery()->getArrayResult();
    }
    
    public function getIncomesForFamilyToArrayQuery($family, $dateTo, $dateFrom, $sidx, $sord) 
    {
        $qb = $this->createQueryBuilder('i')
                ->select('i')
                ->where('i.family = :family')
                ->andWhere('i.date_deleted IS NULL')
                ->andWhere('i.date_created >= :dateFrom')
                ->andWhere('i.date_created <= :dateTo')
                ->addOrderBy($sidx , $sord)
                ->setParameter('family' , $family->getId())
                ->setParameter('dateTo' , $dateTo)
                ->setParameter('dateFrom' , $dateFrom);
        return $qb;
    }
    /**
     * Returns sum of the all incomes for give monnth
     * @param \Budget\BudgetBundle\Entity\FamilyMonth $familyMonth
     * @return type
     */
    public function getTotalSumForMonth(FamilyMonth $familyMonth)
    {
        $qb = $this->createQueryBuilder('i')
                ->select('SUM(i.amount) as total')
                ->where('i.family = :family')
                ->andWhere('i.date_deleted IS NULL')
                ->andWhere('i.date_created >= :dateFrom')
                ->andWhere('i.date_created <= :dateTo')
                ->setParameter('family', $familyMonth->getFamily()->getId())
                ->setParameter('dateTo', $familyMonth->getEnd())
                ->setParameter('dateFrom', $familyMonth->getStart());
        
        $result = $qb->getQuery()->getSingleScalarResult();
        
        if (is_null($result)) {
            return 0;
        }
        
        return $result;
    }
}
